package com.gsx.mapper;

import com.gsx.entity.Blog;
import com.gsx.entity.Blog_Type;
import com.gsx.entity.Blog_Type_User;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface BlogMapper {
    @Select("select * from blog where blog_id=#{id}")
    public Blog getBlog(Long id);
    //分页查询
    //想法：直接关联量表查询  在controller做出判断进行其他操作
    //在mapper.xml文件中会有一些if语句来判断是否执行条件查询
    @Select({"<script>" +
            " SELECT b.blog_id,b.title,b.content,b.firstPicture,b.views,b.appreciation,b.description,b.createTime,t.type_name,b.recommend FROM blog AS b INNER JOIN TYPE AS t ON b.type_id=t.type_id"
            +" <if test='type_id!=null'> and t.type_id=#{type_id} </if> "
            +" <if test='title!=null'> and b.title=#{title} </if> "
            +" <if test='isdelete!=null'> and b.isdelete=#{isdelete} </if> "
            +" order by b.createTime desc "
            +" </script> "
    })
    public List<Blog_Type> getListBlog(Blog_Type blog_type);

    /*获取最新发布6条的博客*/
    @Select("select * from blog order by createTime desc limit 6")
    public List<Blog> getNewBlog();

    /*获取所有博客*/
    @Select("select * from blog")
    public List<Blog>  getAllBlog();
    /*获取blog对应的user*/
    @Select("SELECT b.blog_id,b.title,b.description,b.firstPicture,b.views,b.appreciation,b.shareStatement,b.commentable,b.published,b.recommend,b.updateTime,b.flag,t.type_name,t.type_id,u.user_id,u.nickname,u.avatar  " +
            "FROM blog AS b INNER JOIN USER AS u ON b.user_id=u.user_id " +
            "INNER JOIN TYPE AS t ON b.type_id=t.type_id")
    public List<Blog_Type_User> getBlog_Type_User();

    /*根据id获得user，blog，type三张表的联合信息*/
    @Select("SELECT b.blog_id,b.title,b.description,b.content,b.firstPicture,b.views,b.appreciation,b.shareStatement,b.commentable,b.published,b.recommend,b.updateTime,b.flag,t.type_name,t.type_id,u.user_id,u.nickname,u.avatar  " +
            "FROM blog AS b INNER JOIN USER AS u ON b.user_id=u.user_id " +
            "INNER JOIN TYPE AS t ON b.type_id=t.type_id where b.blog_id=#{id}")
    public Blog_Type_User getB_T_U(Long id);


    /*根据id获得user，blog，type三张表的联合信息,前端专门用于页面展示的*/
    @Select("SELECT b.blog_id,b.title,b.description,b.content,b.firstPicture,b.views,b.appreciation,b.shareStatement,b.commentable,b.published,b.recommend,b.updateTime,b.flag,t.type_name,t.type_id,u.user_id,u.nickname,u.avatar  " +
            "FROM blog AS b INNER JOIN USER AS u ON b.user_id=u.user_id " +
            "INNER JOIN TYPE AS t ON b.type_id=t.type_id where b.blog_id=#{id}")
    public Blog_Type_User getB_T_U_Front(Long id);

    /*更具title名字查询博客*/
    @Select("SELECT b.blog_id,b.title,b.description,b.firstPicture,b.views,b.appreciation,b.shareStatement,b.commentable,b.published,b.recommend,b.updateTime,b.flag,t.type_name,t.type_id,u.user_id,u.nickname,u.avatar " +
            " FROM blog AS b INNER JOIN USER AS u ON b.user_id=u.user_id " +
            " INNER JOIN TYPE AS t ON b.type_id=t.type_id where b.title like '%${title}%'")
    public List<Blog_Type_User> getByBlogTitle(String title);

    @Insert("insert into blog(title,content,firstPicture,views,appreciation,shareStatement,commentable,published,recommend,createTime,updateTime,user_id,type_id,description) " +
            "values(#{title},#{content},#{firstPicture},#{views},#{appreciation},#{shareStatement},#{commentable},#{published},#{recommend},#{createTime},#{updateTime},#{user_id},#{type_id},#{description})")
    public int saveBlog(Blog blog);

    @Update("update blog set title=#{title},content=#{content},firstPicture=#{firstPicture},views=#{views},appreciation=#{appreciation},shareStatement=#{shareStatement}," +
            "commentable=#{commentable},published=#{published},recommend=#{recommend},createTime=#{createTime},updateTime=#{updateTime},type_id=#{type_id},description=#{description} where blog_id=#{blog_id}")
    public int updateBlog(Blog blog);

    @Update("update blog set isdelete=true where blog_id=#{id}")
    public int deleteBlog(Long id);

    @Select("select blog_id from blog")
    public Long[] getId();

    /*获取各个分组中的年份*/
    @Select("SELECT DATE_FORMAT(b.updateTime,'%Y') AS YEAR FROM blog AS b GROUP  BY YEAR ORDER BY YEAR DESC;")
    public String[] getYears();

    /*根据年份来查询对应的表格*/
    @Select("SELECT * FROM blog WHERE DATE_FORMAT(blog.updateTime,'%Y')=#{year}")
    public List<Blog> getByYear(String year);


    @Select("SELECT b.blog_id,b.title,b.description,b.firstPicture,b.views,b.appreciation,b.shareStatement,b.commentable,b.published,b.recommend,b.updateTime,b.flag,t.type_name,t.type_id  " +
            "FROM blog AS b INNER JOIN TYPE AS t ON b.type_id=t.type_id where b.user_id=#{user_id} and b.isdelete=false")
    public List<Blog_Type_User> getSelfBlog(Long user_id);
}
